package dao;

import entity.AccountBalance;
import util.DBUtil;

import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class AccountBalanceDao {

    public List<AccountBalance> getBalancesByOwnerId(Integer ownerId) {
        // 修改SQL，通过JOIN获取费用类型名称和单位
        String sql = "SELECT ab.*, ft.name, ft.unit " +
                "FROM account_balance ab " +
                "JOIN fee_type ft ON ab.fee_type_id = ft.id " +
                "WHERE ab.owner_id = ? " +
                "ORDER BY ft.id";

        List<AccountBalance> balances = new ArrayList<>();

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, ownerId);

            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    AccountBalance balance = new AccountBalance();
                    balance.setId(rs.getInt("id"));
                    balance.setOwnerId(rs.getInt("owner_id"));
                    balance.setFeeTypeId(rs.getInt("fee_type_id"));
                    balance.setBalance(rs.getBigDecimal("balance"));
                    balance.setFeeTypeName(rs.getString("name")); // 从fee_type表获取名称
                    balance.setUnit(rs.getString("unit")); // 从fee_type表获取单位
                    balances.add(balance);
                }
            }
        } catch (Exception e) {
            throw new RuntimeException("查询账户余额失败", e);
        }
        return balances;
    }

    public void updateBalance(Integer ownerId, Integer feeTypeId, BigDecimal amount, Connection conn) throws SQLException {
        // 先检查是否存在余额记录
        String checkSql = "SELECT id FROM account_balance WHERE owner_id = ? AND fee_type_id = ?";
        String updateSql = "UPDATE account_balance SET balance = balance + ? WHERE owner_id = ? AND fee_type_id = ?";
        String insertSql = "INSERT INTO account_balance (owner_id, fee_type_id, balance) VALUES (?, ?, ?)";

        try (PreparedStatement checkStmt = conn.prepareStatement(checkSql)) {
            checkStmt.setInt(1, ownerId);
            checkStmt.setInt(2, feeTypeId);

            boolean exists = false;
            try (ResultSet rs = checkStmt.executeQuery()) {
                exists = rs.next();
            }

            if (exists) {
                // 更新现有记录
                try (PreparedStatement updateStmt = conn.prepareStatement(updateSql)) {
                    updateStmt.setBigDecimal(1, amount);
                    updateStmt.setInt(2, ownerId);
                    updateStmt.setInt(3, feeTypeId);
                    updateStmt.executeUpdate();
                }
            } else {
                // 插入新记录
                try (PreparedStatement insertStmt = conn.prepareStatement(insertSql)) {
                    insertStmt.setInt(1, ownerId);
                    insertStmt.setInt(2, feeTypeId);
                    insertStmt.setBigDecimal(3, amount);
                    insertStmt.executeUpdate();
                }
            }
        }
    }
}